﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using NUnit.Framework;

namespace PropertyExpression.DynamicQueryStaticChecking.Test
{
    public partial class PreTextTemplate1
    {
        private readonly IInnerPartLinkSwitcher innerPartLinkSwitcher;
        private readonly IParamManager<PartLinkParams> param;

        public StringBuilder Builder
        {
            get { return GenerationEnvironment; }
        }

        public PreTextTemplate1(IInnerPartLinkSwitcher innerPartLinkSwitcher, IParamManager<PartLinkParams> param)
        {
            this.innerPartLinkSwitcher = innerPartLinkSwitcher;
            this.param = param;
        }
    }

    public partial class PreTextTemplate2
    {
        private readonly IInnerPartLinkSwitcher innerPartLinkSwitcher;
        private readonly IParamManager<PartLinkParams> param;

        public StringBuilder Builder
        {
            get { return GenerationEnvironment; }
        }

        public PreTextTemplate2(IInnerPartLinkSwitcher innerPartLinkSwitcher, IParamManager<PartLinkParams> param)
        {
            this.innerPartLinkSwitcher = innerPartLinkSwitcher;
            this.param = param;
        }
    }

    [TestFixture]
    public class MainTest
    {
        [Test]
        public void Test2()
        {
            var paramStringBuilder = new StringBuilder();

            var preTextTemplate1 = new PreTextTemplate1(
                new InnerPartLinkSwitcher(
                        new LatestPublicationSwitcher(),
                        new StoredProceduresCreatingParamManager<InnerPartLinkParams>(paramStringBuilder)
                        ),
                new StoredProceduresCreatingParamManager<PartLinkParams>(paramStringBuilder)
                );
            preTextTemplate1.TransformText();

            Console.WriteLine(paramStringBuilder);

            Console.WriteLine(preTextTemplate1.Builder);
        }

        [Test]
        public void Test3()
        {
            var paramStringBuilder = new StringBuilder();

            var preTextTemplate2 = new PreTextTemplate2(
                new InnerPartLinkSwitcher(
                        new LatestPublicationSwitcher(),
                        new StoredProceduresCreatingParamManager<InnerPartLinkParams>(paramStringBuilder)
                        ),
                new StoredProceduresCreatingParamManager<PartLinkParams>(paramStringBuilder)
                );
            preTextTemplate2.TransformText();

            Console.WriteLine(paramStringBuilder);

            Console.WriteLine(preTextTemplate2.Builder);
        }

        [Test]
        public void Test()
        {
            Execute(
                transaction =>
                    {
                        Console.WriteLine("***********************");

                        using (var sqlCommand = new SqlCommand())
                        {
                            sqlCommand.Connection = transaction.Connection;
                            sqlCommand.Transaction = transaction;

                            var stringBuilder = new StringBuilder();
                            FillQuery(
                                stringBuilder,
                                new NoInnerPartLinkSwitcher(),
                                new PartLinkParams(
                                    "4601408,0799901,0799904,0869401,0628907,0799910,0799911,0799912,0799906",
                                    new Guid("5E42EFFD-ED80-4361-855F-734DD690D921"),
                                    new Guid("53E199D3-D655-4B54-80CF-EC70A2093558")
                                    ).AsParamProvider(sqlCommand));
                            sqlCommand.CommandText = stringBuilder.ToString();

                            using (var reader = sqlCommand.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    Console.WriteLine(reader["PartNumber"]);
                                }
                            }
                        }

                        Console.WriteLine("***********************");
                        using (var sqlCommand = new SqlCommand())
                        {
                            sqlCommand.Connection = transaction.Connection;
                            sqlCommand.Transaction = transaction;

                            var stringBuilder = new StringBuilder();
                            FillQuery(
                                stringBuilder,
                                new InnerPartLinkSwitcher(
                                    new NoLatestPublicationSwitcher(),
                                    new InnerPartLinkParams(
                                        new Guid("465D64D8-5599-43BE-B3AB-E4ABC7482E6D"),
                                    "en"
                                        ).AsParamProvider(sqlCommand)
                                    ),
                                new PartLinkParams(
                                    "4601408,0799901,0799904,0869401,0628907,0799910,0799911,0799912,0799906",
                                    new Guid("5E42EFFD-ED80-4361-855F-734DD690D921"),
                                    new Guid("53E199D3-D655-4B54-80CF-EC70A2093558")
                                    ).AsParamProvider(sqlCommand));
                            sqlCommand.CommandText = stringBuilder.ToString();

                            using (var reader = sqlCommand.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    Console.WriteLine(reader["PartNumber"]);
                                }
                            }
                        }
                        Console.WriteLine("***********************");
                        using (var sqlCommand = new SqlCommand())
                        {
                            sqlCommand.Connection = transaction.Connection;
                            sqlCommand.Transaction = transaction;

                            var stringBuilder = new StringBuilder();
                            FillQuery(
                                stringBuilder,
                                new InnerPartLinkSwitcher(
                                    new LatestPublicationSwitcher(),
                                    new InnerPartLinkParams(
                                        new Guid("465D64D8-5599-43BE-B3AB-E4ABC7482E6D"),
                                        "en"
                                        ).AsParamProvider(sqlCommand)
                                    ),
                                new PartLinkParams(
                                    "4601408,0799901,0799904,0869401,0628907,0799910,0799911,0799912,0799906",
                                    new Guid("5E42EFFD-ED80-4361-855F-734DD690D921"),
                                    new Guid("53E199D3-D655-4B54-80CF-EC70A2093558")
                                    ).AsParamProvider(sqlCommand));
                            sqlCommand.CommandText = stringBuilder.ToString();

                            using (var reader = sqlCommand.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    Console.WriteLine(reader["PartNumber"]);
                                }
                            }
                        }
                    }
                );
        }

        [Test]
        public void CreateStoredProceduresTest()
        {
            {
                var stringBuilder = new StringBuilder();

                var paramStringBuilder = new StringBuilder();

                FillQuery(
                    stringBuilder,
                    new NoInnerPartLinkSwitcher(),
                    new StoredProceduresCreatingParamManager<PartLinkParams>(paramStringBuilder)
                    );

                Console.WriteLine(paramStringBuilder);

                Console.WriteLine(stringBuilder);
            }
            Console.WriteLine("***********************");
            {
                var stringBuilder = new StringBuilder();

                var paramStringBuilder = new StringBuilder();

                FillQuery(
                    stringBuilder,
                    new InnerPartLinkSwitcher(
                        new NoLatestPublicationSwitcher(),
                        new StoredProceduresCreatingParamManager<InnerPartLinkParams>(paramStringBuilder)
                        ),
                    new StoredProceduresCreatingParamManager<PartLinkParams>(paramStringBuilder)
                    );

                Console.WriteLine(paramStringBuilder);

                Console.WriteLine(stringBuilder);
            }
            Console.WriteLine("***********************");
            {
                var stringBuilder = new StringBuilder();

                var paramStringBuilder = new StringBuilder();

                FillQuery(
                    stringBuilder,
                    new InnerPartLinkSwitcher(
                        new LatestPublicationSwitcher(),
                        new StoredProceduresCreatingParamManager<InnerPartLinkParams>(paramStringBuilder)
                        ),
                    new StoredProceduresCreatingParamManager<PartLinkParams>(paramStringBuilder)
                    );

                Console.WriteLine(paramStringBuilder);

                Console.WriteLine(stringBuilder);
            }
        }

        private static void FillQuery(
            StringBuilder builder, IInnerPartLinkSwitcher innerPartLinkSwitcher, IParamManager<PartLinkParams> paramManager)
        {
            builder.Append(@"SELECT
    InputPartNumbers.val AS PartNumber
FROM
    dbo.fn_List2TableStr(").Append(paramManager.Param(_ => _.PartList)).Append(@") AS InputPartNumbers
WHERE
    EXISTS ( SELECT
                1
             FROM
                MainPartLink
             WHERE
                MainPartLink.PartNumber = InputPartNumbers.val
                AND MainPartLink.ModelPublicationID =  ").Append(paramManager.Param(_ => _.ModelPublicationID)).Append(@"
                AND MainPartLink.ServicePartsPageID <> ").Append(paramManager.Param(_ => _.ServicePartsPageID)).Append(@")");
            innerPartLinkSwitcher.AddSubQuery((latestPublicationSwitcher, innerPartLinkParamManager) => { builder.Append(@"
    OR EXISTS ( SELECT
                    1
                FROM
                    InnerPartLink
                    INNER JOIN Model
                        ON InnerPartLink.ModelCode = Model.ModelCode
                           AND InnerPartLink.ModelSequence = Model.ModelSequence");
                        latestPublicationSwitcher.AddSubQuery(() => builder.Append(@"
                    INNER JOIN Publication
                        ON InnerPartLink.PublicationID = Publication.PublicationID"));
                        builder.Append(@"
                    INNER JOIN PublicationLocalizedProperties
                        ON InnerPartLink.PublicationID = PublicationLocalizedProperties.ParentObjectID
                           AND PublicationLocalizedProperties.LanguageCode = ").Append(innerPartLinkParamManager.Param(_ => _.LanguageCode)).Append(@"
                WHERE
                    InnerPartLink.PartNumber = InputPartNumbers.val
                    AND Model.ModelID = ").Append(innerPartLinkParamManager.Param(_ => _.ModelID));
                        latestPublicationSwitcher.AddSubQuery(() => builder.Append(@"
                    AND Publication.FollowingPublicationID IS NULL"));
                        builder.Append(@" )");
                    }
                );
        }

        private static TResult Execute<TResult>(Func<SqlTransaction, TResult> func)
        {
            TResult result;
            using (var connection = new SqlConnection(@"Data Source=dell32\MSSQLSERVER2008;Initial Catalog=Hitachi;Integrated Security=True"))
            {
                connection.Open();
                var transaction = connection.BeginTransaction(IsolationLevel.Serializable);
                try
                {
                    result = func(transaction);

                    transaction.Commit();
                }
                catch (Exception exception)
                {
                    try
                    {
                        transaction.Rollback();
                    }
                    catch
                    {
                        throw new ApplicationException("The transaction can not be rolled back.", exception);
                    }
                    throw;
                }
            }
            return result;
        }

        private static void Execute(Action<SqlTransaction> action)
        {
            Execute(transaction =>
                        {
                            action(transaction);
                            return new {};
                        }
                );
        }
    }

    public class PartLinkParams
    {
        public readonly string PartList;
        public readonly Guid ModelPublicationID;
        public readonly Guid ServicePartsPageID;

        public PartLinkParams(string partList, Guid modelPublicationId, Guid servicePartsPageId)
        {
            PartList = partList;
            ModelPublicationID = modelPublicationId;
            ServicePartsPageID = servicePartsPageId;
        }
    }

    public static class PartLinkParamsPropertyExpressionExtension
    {
        public static string PartList(this IParamManager<PartLinkParams> it)
        {
            return it.Param(_ => _.PartList);
        }
        public static string ServicePartsPageID(this IParamManager<PartLinkParams> it)
        {
            return it.Param(_ => _.ServicePartsPageID);
        }
        public static string ModelPublicationID(this IParamManager<PartLinkParams> it)
        {
            return it.Param(_ => _.ModelPublicationID);
        }
    }

    public interface IInnerPartLinkSwitcher
    {
        void AddSubQuery(Action<ILatestPublicationSwitcher, IParamManager<InnerPartLinkParams>> action);
    }

    public class NoInnerPartLinkSwitcher : IInnerPartLinkSwitcher
    {
        public void AddSubQuery(Action<ILatestPublicationSwitcher, IParamManager<InnerPartLinkParams>> action)
        {
            //no-op
        }
    }

    public class InnerPartLinkSwitcher : IInnerPartLinkSwitcher
    {
        private readonly ILatestPublicationSwitcher innerPartLinkSwitcher;
        private readonly IParamManager<InnerPartLinkParams> paramManager;

        public InnerPartLinkSwitcher(ILatestPublicationSwitcher innerPartLinkSwitcher, IParamManager<InnerPartLinkParams> paramManager)
        {
            this.innerPartLinkSwitcher = innerPartLinkSwitcher;
            this.paramManager = paramManager;
        }

        public void AddSubQuery(Action<ILatestPublicationSwitcher, IParamManager<InnerPartLinkParams>> action)
        {
            action(innerPartLinkSwitcher, paramManager);
        }
    }

    public class InnerPartLinkParams
    {
        public readonly Guid ModelID;
        public readonly string LanguageCode;

        public InnerPartLinkParams(Guid modelID, string languageCode)
        {
            ModelID = modelID;
            LanguageCode = languageCode;
        }
    }

    public static class InnerPartLinkParamsPropertyExpressionExtension
    {
        public static string LanguageCode(this IParamManager<InnerPartLinkParams> it)
        {
            return it.Param(_ => _.LanguageCode);
        }
        public static string ModelID(this IParamManager<InnerPartLinkParams> it)
        {
            return it.Param(_ => _.ModelID);
        }
    }

    public interface ILatestPublicationSwitcher
    {
        void AddSubQuery(Action action);
    }

    public class LatestPublicationSwitcher : ILatestPublicationSwitcher
    {
        public void AddSubQuery(Action action)
        {
            action();
        }
    }

    public class NoLatestPublicationSwitcher : ILatestPublicationSwitcher
    {
        public void AddSubQuery(Action action)
        {
            //no-op
        }
    }
}